﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using DVSProject.Models;
namespace DVSProject.Lib
{
    public class ResultRemarkRepo
    {
        public const string m_msg_head = "Result Remark";
        public IEnumerable<ResultRemarkModels> GetResultRemark()
        {

            string SQLCommand;
            SQLCommand = @"select tbresultremark.*,TBTitleResult.TitleResultName from tbresultremark LEFT JOIN TBTitleResult
                           ON tbresultremark.TitleResultId = TBTitleResult.TitleResultId
                           WHERE tbresultremark.Status <> 'Delete'
                           ORDER BY tbresultremark.Id";

            List<ResultRemarkModels> resultRemarks = new List<ResultRemarkModels>();
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        ResultRemarkModels resultRemark = new ResultRemarkModels();
                        resultRemark.Id = (int)reader["Id"];
                        resultRemark.TitleResultId = (int)reader["TitleResultId"];
                        resultRemark.TitleResultName = (reader["TitleResultName"] != DBNull.Value) ? (string)reader["TitleResultName"] : "";
                        resultRemark.ResultRemarkTh = (string)reader["ResultRemarkTh"];
                        resultRemark.ResultRemarkEng = (string)reader["ResultRemarkEng"];
                        resultRemark.Status = (string)reader["Status"];
                        resultRemark.CreateBy = (string)reader["CreateBy"];
                        resultRemark.CreateOn = (DateTime)reader["CreateOn"];
                        resultRemark.UpdateBy = (string)reader["UpdateBy"];
                        resultRemark.UpdateOn = (DateTime)reader["UpdateOn"];

                        resultRemarks.Add(resultRemark);
                    }

                    
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return resultRemarks.ToList();


        }

        public ResultRemarkModels GetResultRemarkById(int id)
        {
            ResultRemarkModels resultRemark = new ResultRemarkModels();
            string SQLCommand;
            SQLCommand = @"select tbresultremark.*,TBTitleResult.TitleResultName from tbresultremark LEFT JOIN TBTitleResult
                           ON tbresultremark.TitleResultId = TBTitleResult.TitleResultId where tbresultremark.Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {

                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        resultRemark.Id = (int)reader["Id"];
                        resultRemark.TitleResultId = (int)reader["TitleResultId"];
                        resultRemark.TitleResultName = (reader["TitleResultName"] != DBNull.Value) ? (string)reader["TitleResultName"] : "";
                        resultRemark.ResultRemarkTh = (string)reader["ResultRemarkTh"];
                        resultRemark.ResultRemarkEng = (string)reader["ResultRemarkEng"];
                        resultRemark.Status = (string)reader["Status"];
                        resultRemark.CreateBy = (string)reader["CreateBy"];
                        resultRemark.CreateOn = (DateTime)reader["CreateOn"];
                        resultRemark.UpdateBy = (string)reader["UpdateBy"];
                        resultRemark.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return resultRemark;
        }

        //public ResultRemarkModels GetResultRemark()
        //{
        //    ResultRemarkModels resultRemark = new ResultRemarkModels();
        //    string SQLCommand;
        //    SQLCommand = @"SELECT * from TBResultRemark";

        //    using (var db = new DBHelper())
        //    {
        //        try
        //        {

        //            var reader = db.ExecuteReader(SQLCommand);
        //            if (reader.Read())
        //            {
        //                resultRemark.Id = (int)reader["Id"];
        //                resultRemark.TitleResultId = (int)reader["TitleResultId"];
        //                resultRemark.TitleResultName = (reader["TitleResultName"] != DBNull.Value) ? (string)reader["TitleResultName"] : "";
        //                resultRemark.ResultRemarkTh = (string)reader["ResultRemarkTh"];
        //                resultRemark.ResultRemarkEng = (string)reader["ResultRemarkEng"];
        //                resultRemark.Status = (string)reader["Status"];
        //                resultRemark.CreateBy = (string)reader["CreateBy"];
        //                resultRemark.CreateOn = (DateTime)reader["CreateOn"];
        //                resultRemark.UpdateBy = (string)reader["UpdateBy"];
        //                resultRemark.UpdateOn = (DateTime)reader["UpdateOn"];
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
        //        }
        //    }
        //    return resultRemark;
        //}

        public bool Create(ResultRemarkModels resultRemark)
        {
            bool result = true;
  
                    string SQLCommand;
                    SQLCommand = @"INSERT into TBResultRemark 
                                    values (@TitleResultId,@ResultRemarkTh,@ResultRemarkEng,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleResultId", resultRemark.TitleResultId);
                    db.AddParameter("@ResultRemarkTh", resultRemark.ResultRemarkTh);
                    db.AddParameter("@ResultRemarkEng", resultRemark.ResultRemarkEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", resultRemark.CreateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                   
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                    return false;
                }
            }

            return result;
        }
        public bool Edit(ResultRemarkModels ResultRemark)
        {
            bool result = true;
            string SQLCommand = @"Update tbresultremark 
                                    Set TitleResultId=@TitleResultId,
                                        ResultRemarkTh=@ResultRemarkTh,
                                        ResultRemarkEng=@ResultRemarkEng,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", ResultRemark.Id);
                    db.AddParameter("@TitleResultId", ResultRemark.TitleResultId);
                    db.AddParameter("@ResultRemarkTh", ResultRemark.ResultRemarkTh);
                    db.AddParameter("@ResultRemarkEng", ResultRemark.ResultRemarkEng);
                    db.AddParameter("@UserID", ResultRemark.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(ResultRemarkModels ResultRemark)
        {
            bool result = true;
            string SQLCommand = @"Update tbresultremark
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", ResultRemark.Id);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", ResultRemark.UpdateBy);
                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}